﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace TAMS.Instructor
{
    public partial class Ins_Examination_Asgn : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string id;

        protected void Page_Load(object sender, EventArgs e)
        {
            ((Menu)Master.FindControl("Menu1")).Items[2].Selected = true;
            id = (string)Session["userName"];

            if (id == null)
                Response.Redirect("../login.aspx");

            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();

            string strSQL = "SELECT SC.cid, SC.sectionNo, SC.secCorsId FROM SectionConsists SC, Educates E WHERE SC.secCorsId = E.secCorsId AND E.fsid =" + id;

            sqlCommand = new MySqlCommand(strSQL, conn);
            readVals = sqlCommand.ExecuteReader();


            DataTable dt;
            dt = new DataTable();
            dt.Columns.Add("secCorsId", typeof(int));
            dt.Columns.Add("name", typeof(string));

            DataRow dr;
            while (readVals.Read())
            {
                dr = dt.NewRow();
                dr["secCorsId"] = readVals.GetInt32("secCorsId");
                dr["name"] = readVals.GetString("cid") + "-" + readVals.GetInt32("sectionNo") ;

                dt.Rows.Add(dr);
            }
            CourseList.DataSource = dt;
            CourseList.DataValueField = "secCorsId";
            CourseList.DataTextField = "name";
            CourseList.DataBind();
            readVals.Close();
        }

        protected void selectClicked(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();

            string strSQL =
            " SELECT T.s_name, T.sid, T.s_email, T.s_entryDate " +
            " FROM GradStudent T, HasSchedule H, Examination E, Schedule S " +
            " WHERE 	E.exId =" + ExamList.SelectedValue +
            "     AND 	E.e_startTime = S.sch_startTime" +
            "     AND 	E.e_endTime = S.sch_endTime" +
            "     AND	S.schId = H.schId" +
            "     AND	H.sid = T.sid ";

            sqlCommand = new MySqlCommand(strSQL, conn);
            readVals = sqlCommand.ExecuteReader();

            SearchTable.DataSource = readVals;
            SearchTable.DataBind();
            readVals.Close();
            conn.Close();
        }

        protected void assignClicked(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();
            string strSQL;
            try
            {
                strSQL = "INSERT INTO Proctors VALUES( " + ExamList.SelectedValue + ", " + SearchTable.SelectedRow.Cells[2].Text + ", " + CourseList.SelectedValue + " )";

                sqlCommand = new MySqlCommand(strSQL, conn);
                sqlCommand.ExecuteNonQuery();

                strSQL =
                " SELECT S.schId " +
                " FROM GradStudent T, HasSchedule H, Examination E, Schedule S " +
                " WHERE 	E.exId =" + ExamList.SelectedValue +
                "     AND 	E.e_startTime = S.sch_startTime" +
                "     AND 	E.e_endTime = S.sch_endTime" +
                "     AND	S.schId = H.schId" +
                "     AND	H.sid = T.sid " +
                "     AND   H.sid = " + SearchTable.SelectedRow.Cells[2].Text;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                readVals.Read();
                int schId = readVals.GetInt32("schId");
                readVals.Close();

                strSQL = "DELETE FROM HasSchedule WHERE schId=" + schId + " AND sid=" + SearchTable.SelectedRow.Cells[2].Text;

                sqlCommand = new MySqlCommand(strSQL, conn);
                sqlCommand.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                string msg = "Assign Error:";
                msg += ex.Message;
                ErrorLabel.Text = msg;
                ErrorLabel.Visible = true;
                goto end;
            }

            ErrorLabel.Text = "Successfully Assigned!";

            end:

            strSQL =
            " SELECT T.s_name, T.sid, T.s_email, T.s_entryDate " +
            " FROM GradStudent T, HasSchedule H, Examination E, Schedule S " +
            " WHERE 	E.exId =" + ExamList.SelectedValue +
            "     AND 	E.e_startTime = S.sch_startTime" +
            "     AND 	E.e_endTime = S.sch_endTime" +
            "     AND	S.schId = H.schId" +
            "     AND	H.sid = T.sid ";

            sqlCommand = new MySqlCommand(strSQL, conn);
            readVals = sqlCommand.ExecuteReader();

            SearchTable.DataSource = readVals;
            SearchTable.DataBind();
            readVals.Close();
            conn.Close();
        }
    }
}